Converting Frequencies

Introduction

In time series analysis, it’s often necessary to aggregate data to different frequencies. For instance, you might want to convert daily stock prices to monthly averages or quarterly GDP data. The xts package in R provides powerful functions like to.monthly, to.quarterly, and to.yearly to facilitate these conversions. However, understanding how these functions work and their potential pitfalls is crucial for accurate analysis.

Setting Up

First, let’s load the necessary libraries and data. We’ll use a dataset containing daily exchange rates of the Euro to USD (XUS).

library(xts)
library(here)

euro <- readRDS(file = here("databases/euro.rds"))

For simplicity, we’ll focus on the year 2022 and the XUS variable:

euro2022 <- euro["2022", "XUS"]

Converting Daily Data to Monthly Data

Using `to.monthly`

The to.monthly function aggregates daily data into monthly data. By default, it creates an OHLC (Open, High, Low, Close) object, which is useful for financial time series but might not be what you need for other types of data.

euro_monthly <- to.monthly(euro2022)
tail(euro_monthly)

         euro2022.Open euro2022.High euro2022.Low euro2022.Close
Jul 2022        1.0428        1.0428       1.0016         1.0218
Aug 2022        1.0261        1.0317       0.9941         1.0057
Sep 2022        0.9944        1.0119       0.9592         0.9799
Oct 2022        0.9824        1.0077       0.9700         0.9883
Nov 2022        0.9874        1.0408       0.9751         1.0405
Dec 2022        1.0522        1.0702       1.0469         1.0702

To create a regular time series (not OHLC), set OHLC = FALSE:

euro_monthly <- to.monthly(euro2022, OHLC = FALSE)
tail(euro_monthly)

            XUS
Jul 2022 1.0218
Aug 2022 1.0057
Sep 2022 0.9799
Oct 2022 0.9883
Nov 2022 1.0405
Dec 2022 1.0702

The to.monthly function uses the last observation of each month:

tail(euro2022)

              XUS
2022-12-23 1.0614
2022-12-26 1.0635
2022-12-27 1.0638
2022-12-28 1.0608
2022-12-29 1.0661
2022-12-30 1.0702

Using `apply.monthly`

If you prefer using an average, first, or custom value for each month, use the apply.monthly function. For example, to get the average:

euro_monthly_avg <- apply.monthly(euro2022, FUN = "mean", na.rm = TRUE)
tail(euro_monthly_avg)

                 XUS
2022-07-29 1.0181238
2022-08-31 1.0121522
2022-09-30 0.9897045
2022-10-31 0.9840190
2022-11-30 1.0210136
2022-12-30 1.0588455

To use the first observation of each month:

euro_monthly_first <- apply.monthly(euro2022, FUN = "first", na.rm = TRUE)
tail(euro_monthly_first)

              XUS
2022-07-29 1.0428
2022-08-31 1.0261
2022-09-30 0.9944
2022-10-31 0.9824
2022-11-30 0.9874
2022-12-30 1.0522

Handling NA Values

Sometimes, using first might return NA if the first day of the month is missing. To handle this, we can create a custom function to select the first non-NA value:

first_non_na_monthly <- function(x) {
  first_non_na <- x[!is.na(x)][1]
  return(first_non_na)
}

euro_monthly_custom <- apply.monthly(euro2022, FUN = first_non_na_monthly)
tail(euro_monthly_custom)

              XUS
2022-07-29 1.0428
2022-08-31 1.0261
2022-09-30 0.9944
2022-10-31 0.9824
2022-11-30 0.9874
2022-12-30 1.0522

Converting to Quarterly Data

Using `to.quarterly`

To aggregate data to quarterly frequency and get the last observation of each quarter:

euro_quarterly <- to.quarterly(euro2022, OHLC = FALSE)
tail(euro_quarterly)

           XUS
2022 Q1 1.1065
2022 Q2 1.0482
2022 Q3 0.9799
2022 Q4 1.0702

Using `apply.quarterly`

To get the average value for each quarter:

euro_quarterly_avg <- apply.quarterly(euro2022, FUN = "mean", na.rm = TRUE)
tail(euro_quarterly_avg)

                XUS
2022-03-31 1.121583
2022-06-30 1.064475
2022-09-30 1.006570
2022-12-30 1.021866

Converting to Annual Data

Using `to.yearly`

To convert data to yearly frequency and get the last observation of each year:

euro_yearly <- to.yearly(euro2022, OHLC = FALSE)
tail(euro_yearly)

              XUS
2022-12-30 1.0702

Using `apply.yearly`

To get the average value for each year:

euro_yearly_avg <- apply.yearly(euro2022, FUN = "mean", na.rm = TRUE)
tail(euro_yearly_avg)

                XUS
2022-12-30 1.053181

Applying Functions to an Entire XTS Object

These functions can also be applied to an entire XTS object containing multiple time series. For instance, to convert the entire euro dataset to yearly frequency:

euro_yearly_all <- to.yearly(euro, na.rm = TRUE, OHLC = FALSE)
tail(euro_yearly_all)

              XUS    FON   FTN   F1W    F1M    F2M    F3M    F6M    F9M     F1
2018-12-28 1.1436  2e-04 2e-04 6e-04 0.0031 0.0058 0.0088 0.0179 0.0270 0.0363
2019-12-30 1.1197  0e+00 2e-04 5e-04 0.0022 0.0041 0.0063 0.0126 0.0188 0.0253
2020-12-30 1.2295  0e+00 5e-04 2e-04 0.0009 0.0016 0.0025 0.0048 0.0074 0.0101
2021-12-31 1.1368 -1e-04 0e+00 1e-04 0.0007 0.0013 0.0021 0.0048 0.0081 0.0125
2022-12-30 1.0702  3e-04 1e-04 5e-04 0.0023 0.0043 0.0066 0.0119 0.0166 0.0201
2023-01-06 1.0644  2e-04 1e-04 5e-04 0.0023 0.0043 0.0064 0.0118 0.0168 0.0213
               F2     F3     F4     F5
2018-12-28 0.0701 0.1017 0.1293 0.1535
2019-12-30 0.0490 0.0723 0.0942 0.1171
2020-12-30 0.0207 0.0327 0.0477 0.0634
2021-12-31 0.0301 0.0484 0.0677 0.0831
2022-12-30 0.0322 0.0393 0.0459 0.0524
2023-01-06 0.0315 0.0378 0.0452 0.0519

Conclusion

Converting time series data to different frequencies is a common task in data analysis. The xts package in R provides flexible tools for this purpose. By understanding the nuances of these functions, you can ensure accurate and meaningful analysis. Whether you’re working with financial data, economic indicators, or any other time series, mastering these techniques will enhance your analytical capabilities.